package statistic;

import database.DatabaseHelper;
import entity.Course;
import entity.Section;

import javax.xml.transform.Result;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.stream.Stream;

public class ScoreDistributionList {
    public ArrayList<Integer> nums;
    public int total = 0;

    private ScoreDistributionList(double[] interval, String attribute, String table, String where, Object... params) throws SQLException {
        DatabaseHelper db = new DatabaseHelper();
        StringBuilder query = new StringBuilder();
        query.append("SELECT\n");
        query.append("CASE\n");
        for (int i = 0; i < interval.length - 1; ++i) {
            query.append("WHEN ").append(interval[i]).append(" <= ").append(attribute).append(" and ").append(attribute).append(" < ").append(interval[i+1]).append(" THEN ").append(i).append("\n");
        }
        query.append("END AS INTERVAL_RANGES,\n");
        query.append("COUNT(*)\n");
        query.append("FROM ").append(table).append("\n");
        query.append("WHERE ").append(where).append("\n");
        query.append("GROUP BY INTERVAL_RANGES;");
        ResultSet rs = db.query(query.toString(), params);
        nums = new ArrayList<>(Collections.nCopies(interval.length - 1, 0));
        while (rs.next()) {
            nums.set(rs.getInt(1), rs.getInt(2));
            total += rs.getInt(2);
        }
        rs.close();
        db.close();
    }

    public ScoreDistributionList(double[] interval) throws SQLException {
        this(interval, "SUB_QUERY.AVG_TOTAL", "(select avg(total) as AVG_TOTAL from score group by stu_id) as SUB_QUERY", "true");
    }

    public ScoreDistributionList(Course cou, double[] interval) throws SQLException {
        this(interval, "score.total", "score join section on score.sec_id = section.id", "cou_id = ?", cou.getId());
    }

    public ScoreDistributionList(Course cou, String semester, double[] interval) throws SQLException {
        this(interval, "score.total", "score join section on score.sec_id = section.id", "cou_id = ? and semester = ?", cou.getId(), semester);
    }

    public ScoreDistributionList(Section sec, double[] interval) throws SQLException {
        this(interval, "score.total", "score join section on score.sec_id = section.id", "sec_id = ?", sec.getId());
    }
}
